package com.monitor.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.monitor.entity.ErpOrderdMateriel;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 需求汇总物料表 Mapper 接口
 * </p>
 *
 * @author LX
 * @since 2018-09-11
 */
public interface ErpOrderdMaterielMapper extends BaseMapper<ErpOrderdMateriel> {



    @Select("select materiel_name,materiel_brand,materiel_Specifications,materiel_model FROM erp_orderd_materiel GROUP BY materiel_name,materiel_brand,materiel_Specifications,materiel_model")
    List<Map<String,String>> getMaterielGroup();
    
    @Select("SELECT om.id as mo_id,o.id as order_id,o.dt,c.cus_name,o.b_manager,om.materiel_name,om.materiel_brand,om.materiel_Specifications,om.materiel_model,om.materiel_place,om.materiel_unit,om.materiel_amount FROM erp_orderd_materiel as om LEFT JOIN erp_orderd as o ON om.order_id=o.id LEFT JOIN erp_customer as c ON c.id=o.customer_id WHERE o.dt>=#{startTime} and o.dt<=#{endTime} and o.customer_id=#{custId} order by o.dt desc")
    public List<Map<String, Object>> selectByTime(@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("custId")int custId);
    
    @Select("SELECT * from erp_orderd_materiel om	where om.order_id=#{id} and om.work_state=0	and om.materiel_name=#{name}	and om.materiel_brand=? 	and om.materiel_specifications=?	and om.materiel_model=?	and om.materiel_place=? 	and om.materiel_state=? 	and om.materiel_unit=?")
    public List<ErpOrderdMateriel> selectByMateriel(@Param("id")int id,@Param("name")String name,@Param("brand")String brand,@Param("specifications")String specifications,@Param("model")String model,@Param("place")String place,@Param("state")String state,@Param("unit")String unit);

    @Select("SELECT om.id,om.materiel_name,om.materiel_brand,om.materiel_Specifications,om.materiel_model,om.materiel_place,om.materiel_state,om.materiel_unit,IFNULL(SUM(om.materiel_amount),0) as xu FROM erp_orderd_materiel as om LEFT JOIN erp_orderd as o ON om.order_id=o.id WHERE om.work_state=0 and om.materiel_name is NOT NULL AND om.materiel_name!='' AND o.dt>=#{startTime} AND o.dt<=#{endTime} AND o.customer_id=#{custId} GROUP BY om.materiel_name,om.materiel_brand,om.materiel_Specifications,om.materiel_model,om.materiel_place,om.materiel_state,om.materiel_unit")
    public List<Map<String, Object>> selectByType(@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("custId")int custId);
    
    @Select("SELECT om.id,om.materiel_name,om.materiel_brand,om.materiel_Specifications,om.materiel_model,om.materiel_place,om.materiel_state,om.materiel_unit,IFNULL(SUM(om.materiel_amount),0) as xu FROM erp_orderd_materiel as om LEFT JOIN erp_orderd as o ON om.order_id=o.id WHERE om.work_state=0 and om.materiel_name is NOT NULL AND om.materiel_name!=''  AND o.customer_id=#{custId} GROUP BY om.materiel_name,om.materiel_brand,om.materiel_Specifications,om.materiel_model,om.materiel_place,om.materiel_state,om.materiel_unit")
    public List<Map<String, Object>> selectByCustId(@Param("custId")int custId);
    
    //需求汇总 订单排列查询方法
    @Select("SELECT * FROM erp_orderd_materiel as om LEFT JOIN erp_orderd as o ON o.id=om.order_id WHERE o.dt>=#{startTime} AND o.dt<=#{endTime} AND o.customer_id=#{custId} ORDER BY o.dt DESC")
    public List<Map<String, Object>> selectByOrderList(@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("custId")int custId);
}
